package io.fredia.common.utils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * 新excel导入工具
 * 
 * @author : Fredia
 * @since : 2018年3月6日
 * @version : v1.0.0
 */
public class NewExcelUtils {

	public static <T> List<T> importCSV(InputStream in, String encode, Class dataClass, int startRow) throws Exception {

		String str = "";
		List<String> csvList = new ArrayList<>();
		if (encode == null || encode.equals("")) {
			// 默认以utf-8形式
			encode = "GBK";
		}
		BufferedReader reader = new BufferedReader(new InputStreamReader(in, "GBK"));
		StringBuffer sb = new StringBuffer();
		while ((str = reader.readLine()) != null) {
			csvList.add(str);
		}
		List<String> headerList = Arrays.asList(csvList.get(startRow).split(","));
		// 构建 Excel表头名 - 字段名 的Map
		// Excel表头名 - Annotation 的Map
		Field[] fields = dataClass.getDeclaredFields();
		Map<String, Field> headerFieldMap = new HashMap<>();
		Map<String, ExcelColumn> headerExcelColumnMap = new HashedMap();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelColumn.class)) {
				ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
				headerFieldMap.put(excelColumn.name(), field);
				headerExcelColumnMap.put(excelColumn.name(), excelColumn);
			}
		}

		// 读取数据主体
		List<T> dataList = new LinkedList<>();
		for (int j = startRow + 1; j <= csvList.size() - 2; j++) {
			String row = csvList.get(j);
			try {
				T data = (T) dataClass.newInstance();
				List<String> cellList = Arrays.asList(row.split(","));
				for (int k = 0; k < cellList.size(); k++) {
					String cellVale = cellList.get(k);
					String header = headerList.get(k);
					// 对应字段
					Field field = headerFieldMap.get(header);
					ExcelColumn excelColumn = headerExcelColumnMap.get(header);
					// 根据字段类型和注解类型信息，反射设置data字段值
					if (excelColumn != null) {
						try {
							setDataValueCSV(data, excelColumn, field, cellVale);
						} catch (Exception e) {
							e.printStackTrace();
							field.set(data, null);
						}
					}
				}
				dataList.add(data);
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}

	/**
	 * 导入excel(HSSFWorkbook)
	 * 
	 * @param workbook
	 * @param dataClass
	 * @param startRow
	 * @return
	 * @throws IOException
	 * @author : Fredia
	 * @since : 2018年3月6日
	 * @return :List<T>
	 */
	public static <T> List<T> importExcel1(HSSFWorkbook workbook, Class dataClass, int startRow) throws IOException {

		HSSFSheet sheet = workbook.getSheetAt(0);

		// 读取表头
		Row sheetHeader = sheet.getRow(startRow);
		List<String> headerList = new ArrayList<>();
		for (int i = 0; i < sheetHeader.getLastCellNum(); i++) {
			String header = sheetHeader.getCell(i).getStringCellValue();
			headerList.add(org.springframework.util.StringUtils.trimAllWhitespace(header));
		}

		// 构建 Excel表头名 - 字段名 的Map
		// Excel表头名 - Annotation 的Map
		Field[] fields = dataClass.getDeclaredFields();
		Map<String, Field> headerFieldMap = new HashMap<>();
		Map<String, ExcelColumn> headerExcelColumnMap = new HashedMap();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelColumn.class)) {
				ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
				headerFieldMap.put(excelColumn.name(), field);
				headerExcelColumnMap.put(excelColumn.name(), excelColumn);
			}
		}

		// 读取数据主体
		List<T> dataList = new LinkedList<>();
		for (int j = startRow + 1; j <= sheet.getLastRowNum() - 1; j++) {
			Row row = sheet.getRow(j);
			try {
				T data = (T) dataClass.newInstance();
				for (int i = 0; i < row.getLastCellNum(); i++) {
					Cell cell = row.getCell(i);
					String header = headerList.get(i);
					// 对应字段
					Field field = headerFieldMap.get(header);
					// 对应Excel注解
					ExcelColumn excelColumn = headerExcelColumnMap.get(header);
					// 根据字段类型和注解类型信息，反射设置data字段值
					if (excelColumn != null) {
						try {
							setDataValue(data, excelColumn, field, cell);
						} catch (Exception e) {
							e.printStackTrace();
							field.set(data, null);
						}
					}
				}
				dataList.add(data);
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}

	/**
	 * 导入Excel为对象列表 流的形式
	 * 
	 * @param inputStream
	 * @param dataClass
	 * @param startRow
	 * @return
	 * @throws IOException
	 * @author : Fredia
	 * @since : 2018年3月6日
	 * @return :List<T>
	 */
	public static <T> List<T> importExcel(InputStream inputStream, Class dataClass, int startRow) throws IOException {
		if (inputStream == null) {
			return null;
		}

		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		HSSFSheet sheet = workbook.getSheetAt(0);

		// 读取表头
		Row sheetHeader = sheet.getRow(startRow);
		List<String> headerList = new ArrayList<>();
		for (int i = 0; i < sheetHeader.getLastCellNum(); i++) {
			String header = sheetHeader.getCell(i).getStringCellValue();
			headerList.add(org.springframework.util.StringUtils.trimAllWhitespace(header));
		}

		// 构建 Excel表头名 - 字段名 的Map
		// Excel表头名 - Annotation 的Map
		Field[] fields = dataClass.getDeclaredFields();
		Map<String, Field> headerFieldMap = new HashMap<>();
		Map<String, ExcelColumn> headerExcelColumnMap = new HashedMap();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelColumn.class)) {
				ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
				headerFieldMap.put(excelColumn.name(), field);
				headerExcelColumnMap.put(excelColumn.name(), excelColumn);
			}
		}

		// 读取数据主体
		List<T> dataList = new LinkedList<>();
		for (int j = startRow + 1; j <= sheet.getLastRowNum() - 1; j++) {
			Row row = sheet.getRow(j);
			try {
				T data = (T) dataClass.newInstance();
				for (int i = 0; i < row.getLastCellNum(); i++) {
					Cell cell = row.getCell(i);
					String header = headerList.get(i);
					// 对应字段
					Field field = headerFieldMap.get(header);
					// 对应Excel注解
					ExcelColumn excelColumn = headerExcelColumnMap.get(header);
					// 根据字段类型和注解类型信息，反射设置data字段值
					if (excelColumn != null) {
						try {
							setDataValue(data, excelColumn, field, cell);
						} catch (Exception e) {
							e.printStackTrace();
							field.set(data, null);
						}
					}
				}
				dataList.add(data);
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}

	/**
	 * 设置字段值
	 *
	 * @param data
	 *            导入模型类
	 * @param excelColumn
	 *            excel注解信息
	 * @param field
	 *            字段
	 * @param cell
	 *            excel单元格
	 * @throws ParseException
	 * @throws IllegalAccessException
	 * @author : Fredia
	 * @since : 2018年3月6日
	 * @return :void
	 */
	private static void setDataValue(Object data, ExcelColumn excelColumn, Field field, Cell cell)
			throws ParseException, IllegalAccessException {
		field.setAccessible(true);
		if (field.getType() == Date.class) {
			// 时间
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				field.set(data, cell.getDateCellValue());
			} else {
				if (StringUtils.isEmpty(excelColumn.format())) {
					field.set(data, DateUtils.parseDate(cell.getStringCellValue()));
				} else {
					field.set(data, DateUtils.parseDate(cell.getStringCellValue(), excelColumn.format()));
				}
			}
		} else if (excelColumn.type().isEnum()) {
			// 枚举，excel中存储的是描述值，转化为对应int类
			Object cellValue = cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? cell.getNumericCellValue()
					: cell.getStringCellValue();
			Integer enumValue = (Integer) EnumUtils.getValueByDescription(cellValue, excelColumn.type());
			field.set(data, enumValue);
		} else if (field.getType() == Integer.class) {
			// 整型
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				field.set(data, (int) cell.getNumericCellValue());
			} else {
				field.set(data, Integer.parseInt(cell.getStringCellValue()));
			}
		} else if (field.getType() == Long.class) {
			// 长整型
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				field.set(data, (long) cell.getNumericCellValue());
			} else {
				field.set(data, Long.parseLong(cell.getStringCellValue()));
			}
		} else if (field.getType() == Double.class) {
			// 浮点数
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				field.set(data, cell.getNumericCellValue());
			} else {
				field.set(data, Double.parseDouble(cell.getStringCellValue()));
			}
		} else if (field.getType() == String.class) {
			// 字符串
			Object cellValue = cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? cell.getNumericCellValue()
					: cell.getStringCellValue();
			field.set(data, cellValue);
		}
	}

	private static void setDataValueCSV(Object data, ExcelColumn excelColumn, Field field, String cell)
			throws ParseException, IllegalAccessException {
		field.setAccessible(true);
		if (field.getType() == Date.class) {
			// 时间
			if (StringUtils.isEmpty(excelColumn.format())) {
				field.set(data, DateUtils.parseDate(cell));
			} else {
				field.set(data, DateUtils.parseDate(cell, excelColumn.format()));
			}
		} else if (excelColumn.type().isEnum()) {
			// 枚举，excel中存储的是描述值，转化为对应int类
			Object cellValue = cell;
			Integer enumValue = (Integer) EnumUtils.getValueByDescription(cellValue, excelColumn.type());
			field.set(data, enumValue);
		} else if (field.getType() == Integer.class) {
			// 整型
			if (StringUtils.isNotBlank(cell)) {
				field.set(data, Integer.parseInt(cell));
			} else {
				field.set(data, 0);
			}

		} else if (field.getType() == Long.class) {
			// 长整型
			if (StringUtils.isNotBlank(cell)) {
				field.set(data, Long.parseLong(cell));
			} else {
				field.set(data, 0L);
			}
		} else if (field.getType() == Double.class) {
			// 浮点数
			if (StringUtils.isNotBlank(cell)) {
				field.set(data, Double.parseDouble(cell));
			} else {
				field.set(data, 0.00D);
			}
		} else if (field.getType() == String.class) {
			// 字符串
			field.set(data, cell);
		}
	}
}
